using System;
using System.Collections.Generic;
using System.Text;
using System.Web.Security;
using AAM.SQL;
using System.Configuration.Provider;
using System.Data.Common;

namespace CMS.BOP
{
    public class SqlBizRoleProvider : RoleProvider
    {

        private Connection connection;
        private string applicationName;
        private bool requiresQuestionAndAnswer;
        private bool requiresUniqueEmail;
        private bool enablePasswordReset;
        private bool enablePasswordRetrieval;

        public override void Initialize(string name, System.Collections.Specialized.NameValueCollection config)
        {
            base.Initialize(name, config);
            string connectionStringName = config["connectionStringName"];
            connection = ConnectionManager.Instance.OpenConnection(connectionStringName);
            requiresQuestionAndAnswer = (config["requiresQuestionAndAnswer"] == "true");
            requiresUniqueEmail = (config["requiresUniqueEmail"] == "true");
            enablePasswordReset = (config["enablePasswordReset"] == "true");
            enablePasswordRetrieval = (config["enablePasswordRetrieval"] == "true");
        }
        public override void AddUsersToRoles(string[] usernames, string[] roleNames)
        {
            //TODO check if values exists
            Command command = connection.CreateCommand("INSERT INTO MembershipUsersRoles(UserName,RoleName) VALUES(?,?)");
            DbParameter uName = command.AddParameter(System.Data.DbType.String, "@UserName");
            DbParameter rName = command.AddParameter(System.Data.DbType.String, "@RoleName");
            foreach (string username in usernames)
            {
                foreach (string rolename in roleNames)
                {
                    uName.Value = username;
                    rName.Value = rolename;
                    command.ExecuteNonQuery();
                }
            }
        }
        public override string ApplicationName
        {
            get { return applicationName; }
            set { applicationName = value; }
        }
        public override void CreateRole(string roleName)
        {
            if (roleName == null) throw new ArgumentNullException();
            if (roleName.Trim().Length == 0 || roleName.Contains(",")) throw new ArgumentException();
            Command command = connection.CreateCommand("INSERT INTO Roles(RoleName) VALUES(?)");
            command.AddParameter(roleName);
            int result = command.ExecuteNonQuery();
            command.Dispose();
        }
        public void RemoveUsersFromRole(string roleName)
        {
            Command command = connection.CreateCommand("DELETE FROM MembershipUsersRoles WHERE RoleName=?");
            command.AddParameter(roleName);
            int result = command.ExecuteNonQuery();
            command.Dispose();
        }
        public int CountUsersInRole(string roleName)
        {
            Command command = connection.CreateCommand("SELECT COUNT(*) FROM MembershipUsersRoles WHERE RoleName=?");
            command.AddParameter(roleName);
            object obj = command.ExecuteScalar();
            command.Dispose();
            return obj == null ? 0 : (int)obj;
        }
        public override bool DeleteRole(string roleName, bool throwOnPopulatedRole)
        {
            if (throwOnPopulatedRole)
            {
                int count = CountUsersInRole(roleName);
                if (count > 0) throw new ProviderException();
            }
            RemoveUsersFromRole(roleName);
            Command command = connection.CreateCommand("DELETE FROM Roles WHERE RoleName=?");
            command.AddParameter(roleName);
            int result = command.ExecuteNonQuery();
            command.Dispose();
            return result > 0;
        }
        public override string[] FindUsersInRole(string roleName, string usernameToMatch)
        {
            Command command = connection.CreateCommand("SELECT UserName FROM MembershipUsersRoles WHERE RoleName=? AND UserName LIKE ?");
            command.AddParameter(roleName);
            command.AddParameter(usernameToMatch);
            DataReader reader = command.ExecuteReader();
            List<string> list = new List<string>();
            while (reader.Read())
            {
                list.Add((string)reader["UserName"]);
            }
            reader.Close();
            command.Dispose();
            return list.ToArray();
        }
        public override string[] GetAllRoles()
        {
            Command command = connection.CreateCommand("SELECT RoleName FROM Roles");
            DataReader reader = command.ExecuteReader();
            List<string> list = new List<string>();
            while (reader.Read())
            {
                list.Add((string)reader["RoleName"]);
            }
            reader.Close();
            command.Dispose();
            return list.ToArray();
        }
        public override string[] GetRolesForUser(string username)
        {
            Command command = connection.CreateCommand("SELECT RoleName FROM MembershipUsersRoles WHERE UserName=?");
            command.AddParameter(username);
            DataReader reader = command.ExecuteReader();
            List<string> list = new List<string>();
            while (reader.Read())
            {
                list.Add((string)reader["RoleName"]);
            }
            reader.Close();
            command.Dispose();
            return list.ToArray();
        }
        public override string[] GetUsersInRole(string roleName)
        {
            Command command = connection.CreateCommand("SELECT UserName FROM MembershipUsersRoles WHERE RoleName=?");
            command.AddParameter(roleName);
            DataReader reader = command.ExecuteReader();
            List<string> list = new List<string>();
            while (reader.Read())
            {
                list.Add((string)reader["UserName"]);
            }
            reader.Close();
            command.Dispose();
            return list.ToArray();
        }
        public override bool IsUserInRole(string username, string roleName)
        {
            Command command = connection.CreateCommand("SELECT COUNT(*) FROM MembershipUsersRoles mr WHERE RoleName=? AND UserName=?");
            command.AddParameter(roleName);
            command.AddParameter(username);
            object obj = command.ExecuteScalar();
            command.Dispose();
            return obj == null ? false : ((int)obj) > 0;
        }
        public override void RemoveUsersFromRoles(string[] usernames, string[] roleNames)
        {
            Command command = connection.CreateCommand("DELETE FROM MembershipUsersRoles WHERE UserName=? AND RoleName=?");
            DbParameter uName = command.AddParameter(System.Data.DbType.String, "@UserName");
            DbParameter rName = command.AddParameter(System.Data.DbType.String, "@RoleName");
            foreach (string username in usernames)
            {
                foreach (string rolename in roleNames)
                {
                    uName.Value = username;
                    rName.Value = rolename;
                    command.ExecuteNonQuery();
                }
            }
        }
        public override bool RoleExists(string roleName)
        {
            Command command = connection.CreateCommand("SELECT COUNT(*) FROM Roles WHERE RoleName=?");
            command.AddParameter(roleName);
            object obj = command.ExecuteScalar();
            command.Dispose();
            return obj == null ? false : ((int)obj) > 0;
        }
    }
}
